package com.test.example.command;


import java.sql.*;
import java.util.Scanner;

public class QueryCommandImpl implements Command {
    @Override
    public void execute() {
        System.out.println("请输入查询价格的上限");
        Scanner scanner = new Scanner(System.in);
        Integer maxPrice = scanner.nextInt();
        System.out.println("请输入查询价格的下限");
        scanner = new Scanner(System.in);
        Integer minPrice = scanner.nextInt();
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            String dbUrl = "jdbc:mysql://192.168.7.190:3306/yulei_test?" +
                    "useSSL=false&useUnicode=true&characterEncoding=UTF-8" +
                    "&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true";
            String dbUsername = "testreadwrite";
            String dbPassword = "testrw190!@#";
            connection = DriverManager.getConnection(dbUrl, dbUsername, dbPassword);
            String sql = "select * from sy_test_table where price <? and price >?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,maxPrice);
            preparedStatement.setInt(2,minPrice);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                Integer id = resultSet.getInt(1);
                String name = resultSet.getString("name");
                Float price = resultSet.getFloat("price");
                String desp = resultSet.getString("desp");
                System.out.println(id + "-" + name + "-" + price + "-" + desp);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (resultSet != null) {
                    resultSet.isClosed();
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            try {
                if (preparedStatement != null) {
                    preparedStatement.isClosed();
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            try {
                if (connection != null && connection.isClosed() == false) {
                    connection.isClosed();
                }
            } catch (Exception throwables) {
                throwables.printStackTrace();
            }
        }
    }
}
